package com.dily.scaffold.common.utils;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;

/**
 * Date: 2022-10-18 星期二
 * Time: 16:16
 * Author: Dily_Su
 * Remark:
 */
public class DatabaseUtils {

    public static void main(String[] args) throws Exception {

        String driver = "com.mysql.cj.jdbc.Driver";
        String url = "jdbc:mysql://172.16.20.201:3306/erp?serverTimezone=GMT";
        String user = "root";
        String password = "root";
        //生成的excel文件存储路径
        String filePath = "C:/Users/10055/Desktop/db/";
        //数据库的所有表生成excel
//        createDataBaseExcels(driver, url, user, password,filePath);

        //数据库的 file_info 表生成 excel
//        createDataBaseExcel(driver, url, user, password, "app_user",filePath);
    }

    /**
     * @Description : 生成数据库的所有表的excel
     */
    public static void createDataBaseExcels(String driver, String url, String user, String password, String filePath) throws Exception {
        JSONArray array = getTablesInfo(driver, url, user, password);
        String[] strArray = {"名称", "类型", "长度", "必填", "注释", "备注"};
        createDataBaseExcels(array, strArray, filePath);
    }

    /**
     * @Description : 生成指定单个表的excel
     */
    public static void createDataBaseExcel(String driver, String url, String user, String password, String tableName, String filePath) throws Exception {
        String[] strArray = {"名称", "类型", "长度", "必填", "注释", "备注"};
        JSONArray jsonArray = getTableInfo(driver, url, user, password, tableName);
        String fileName = filePath + tableName + ".xls";
        createDataBaseExcel(jsonArray, strArray, fileName);
        System.out.println(tableName + "表格生成成功,文件路径:  " + fileName);
    }

    /**
     * @param jsonArray 数据库信息：getTablesInfo返回值
     * @param strArray  excel 标头信息
     * @param filePath  文件存储地址
     */
    public static void createDataBaseExcels(JSONArray jsonArray, String[] strArray, String filePath) throws IOException {
        for (int i = 0; i < jsonArray.size(); i++) {
            JSONObject jsonObject = jsonArray.getJSONObject(i);
            String table = jsonObject.getString("table");
            JSONArray fields = jsonObject.getJSONArray("fields");
            String fileName = filePath + table + ".xls";
            createDataBaseExcel(fields, strArray, fileName);
            System.out.println(table + "表格生成成功,文件路径:  " + fileName);
        }
    }

    /**
     * @param jsonArray 数据库信息：getTablesInfo返回值
     * @param strArray  excel 标头信息
     * @param fileName  文件存储地址
     */
    public static void createDataBaseExcel(JSONArray jsonArray, String[] strArray, String fileName) throws IOException {
        // 创建一个webbook，对应一个Excel文件
        HSSFWorkbook wb = new HSSFWorkbook();
        // 在webbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = wb.createSheet("sheet1");
        // 在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
        HSSFRow row = sheet.createRow(0);
        // 添加标题行
        HSSFCell cell = null;
        for (int i = 0; i < strArray.length; i++) {
            // 获取行内对应单元格
            cell = row.createCell(i);
            // 单元格赋值
            cell.setCellValue(strArray[i]);
        }

        // 写入实体数据，实际应用中这些数据从数据库得到,list中字符串的顺序必须和数组strArray中的顺序一致
        int i = 0;
        for (int j = 0; j < jsonArray.size(); j++) {
            JSONObject json = jsonArray.getJSONObject(j);
            i++;
            row = sheet.createRow(i);
            String name = json.getString("name");
            String type = json.getString("type").toLowerCase();
            Integer size = json.getInteger("size");
            String isNeed = "是";
            if (json.getBoolean("isNull").equals(true)) {
                isNeed = "否";
            }
            String remarks = json.getString("remarks");
            // 添加数据行

            for (int z = 0; z < strArray.length; z++) {
                cell = row.createCell(z);
                // 获取行内对应单元格
                // 单元格赋值
                if (z == 0) {
                    cell.setCellValue(name);
                } else if (z == 1) {
                    cell.setCellValue(type);
                } else if (z == 2) {
                    cell.setCellValue(size);
                } else if (z == 3) {
                    cell.setCellValue(isNeed);
                } else if (z == 4) {
                    cell.setCellValue(remarks);
                }
            }
        }
//            // 第六步，将文件存到指定位置
        FileOutputStream fout = new FileOutputStream(fileName);
        try {
            wb.write(fout);
            fout.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            fout.close();
        }
    }

    /**
     * 读取数据库
     * 所有表结构
     */
    public static JSONArray getTablesInfo(String driver, String url, String user, String password) throws Exception {
        JSONArray array = new JSONArray();
        Class.forName(driver);
        Connection connection = DriverManager.getConnection(url, user, password);
        DatabaseMetaData metaData = connection.getMetaData();
//从元数据中获取到所有的表名
        String[] types = {"TABLE"};
        //mysql-connector-java 6.0以下用这个方法
        //rs = db.getTables(null, null, null, new String[] { "TABLE" });

        //mysql-connector-java 6.x用这个方法
        ResultSet tableResultSet = metaData.getTables(connection.getCatalog(), connection.getCatalog(), "%", types);

        while (tableResultSet.next()) {
            JSONObject json = new JSONObject();
            String tableName = tableResultSet.getString("TABLE_NAME");
            json.put("table", tableName);
            // 获取表字段结构
            ResultSet columnResultSet = metaData.getColumns(null, "%", tableName, "%");
            JSONArray fields = new JSONArray();
            while (columnResultSet.next()) {
                JSONObject field = new JSONObject();
                // 字段名称
                String columnName = columnResultSet.getString("COLUMN_NAME");
                boolean repeat = false;
                //循环查看当前值是否重复
                for (int i = 0; i < fields.size(); i++) {
                    JSONObject jsonObject = fields.getJSONObject(i);
                    String column_name = jsonObject.getString("name");
                    if (columnName.equals(column_name)) {
                        repeat = true;
                        break;
                    }
                }
                if (repeat) {
                    continue;
                }
                field.put("name", columnName);
                // 数据类型
                String columnType = columnResultSet.getString("TYPE_NAME");
                field.put("type", columnType);
                // 字段长度
                int datasize = columnResultSet.getInt("COLUMN_SIZE");
                field.put("size", datasize);
                // 小数部分位数
                int digits = columnResultSet.getInt("DECIMAL_DIGITS");
                field.put("digits", digits);
                // 是否可为空 1代表可空 0代表不可为空
                int nullable = columnResultSet.getInt("NULLABLE");
                if (nullable == 1) {
                    field.put("isNull", true);
                } else {
                    field.put("isNull", false);
                }
                // 描述
                String remarks = columnResultSet.getString("REMARKS");
                field.put("remarks", remarks);
                fields.add(field);
            }
            json.put("fields", fields);
            array.add(json);
        }
        return array;
    }

    /**
     * 读取数据库
     * tableName表结构
     */
    public static JSONArray getTableInfo(String driver, String url, String user, String password, String tableName) throws Exception {
        Class.forName(driver);
        Connection connection = DriverManager.getConnection(url, user, password);
        DatabaseMetaData metaData = connection.getMetaData();
        //从元数据中获取到所有的表名
        String[] types = {"TABLE"};
        //mysql-connector-java 6.0以下用这个方法
        //rs = db.getTables(null, null, null, new String[] { "TABLE" });
        //mysql-connector-java 6.x用这个方法
        ResultSet tableResultSet = metaData.getTables(connection.getCatalog(), connection.getCatalog(), "%", types);
        while (tableResultSet.next()) {
            JSONObject json = new JSONObject();
            String table = tableResultSet.getString("TABLE_NAME");
            if (table.equals(tableName)) {
                ResultSet columnResultSet = metaData.getColumns(null, "%", tableName, "%");
                JSONArray fields = new JSONArray();
                while (columnResultSet.next()) {
                    JSONObject field = new JSONObject();
                    // 字段名称
                    String columnName = columnResultSet.getString("COLUMN_NAME");
                    boolean repeat = false;
                    //循环查看当前值是否重复
                    for (int i = 0; i < fields.size(); i++) {
                        JSONObject jsonObject = fields.getJSONObject(i);
                        String column_name = jsonObject.getString("name");
                        if (columnName.equals(column_name)) {
                            repeat = true;
                            break;
                        }
                    }
                    if (repeat) {
                        continue;
                    }
                    field.put("name", columnName);
                    // 数据类型
                    String columnType = columnResultSet.getString("TYPE_NAME");
                    field.put("type", columnType);
                    // 字段长度
                    int datasize = columnResultSet.getInt("COLUMN_SIZE");
                    field.put("size", datasize);
                    // 小数部分位数
                    int digits = columnResultSet.getInt("DECIMAL_DIGITS");
                    field.put("digits", digits);
                    // 是否可为空 1代表可空 0代表不可为空
                    int nullable = columnResultSet.getInt("NULLABLE");
                    if (nullable == 1) {
                        field.put("isNull", true);
                    } else {
                        field.put("isNull", false);
                    }
                    // 描述
                    String remarks = columnResultSet.getString("REMARKS");
                    field.put("remarks", remarks);

                    fields.add(field);
                }
                return fields;
            }
        }
        return null;
    }
}

